﻿using System;
using System.Data;
using System.Text;
using System.Windows.Forms;
using BuildSQLServerInsertScript.Common;

namespace BuildSQLServerInsertScript
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btnBuild_Click(object sender, EventArgs e)
        {
            DataSet ds = SqlHelper.ExecuteDataset(txtConnectionString.Text.Trim(), CommandType.Text, txtSelectSQL.Text.Trim());
            if (ds != null && ds.Tables.Count > 0)
            {
                string tableName = txtTableName.Text.Trim();
                StringBuilder sql = new StringBuilder();
                StringBuilder fieldList = new StringBuilder();
                StringBuilder valueList = new StringBuilder();
                string value = string.Empty;
                DataTable dt = ds.Tables[0];
                foreach (DataColumn dc in dt.Columns)
                {
                    if(string.IsNullOrWhiteSpace(dc.ColumnName) == false)
                        fieldList.AppendFormat("{0},", dc.ColumnName);
                }
                if (fieldList.Length > 0)
                {
                    fieldList.Remove(fieldList.Length - 1, 1);
                    foreach (DataRow dr in dt.Rows)
                    {
                        foreach (DataColumn dc in dt.Columns)
                        {
                            switch (dc.DataType.Name)
                            {
                                case "DateTime":
                                    valueList.AppendFormat("'{0:yyyy-MM-dd HH:mm:ss}',", dr[dc.ColumnName]);
                                    break;
                                default:
                                    value = Convert.ToString(dr[dc.ColumnName]);
                                    if (value != null) value = value.Replace("'", "''");
                                    valueList.AppendFormat("'{0}',", value);
                                    break;
                            }
                        }
                        valueList.Remove(valueList.Length - 1, 1);

                        sql.AppendFormat("INSERT INTO {0} ({1}) VALUES ({2});{3}",tableName, fieldList, valueList, Environment.NewLine);

                        valueList.Length = 0;
                    }
                    txtOutputScript.Text = sql.ToString();
                }

            }
        }

    }
}
